/**
 * 数据迁移脚本：将Message和UserRoomGift表中的用户快照数据迁移到UserSnapshot表
 * 
 * 使用方法：
 * node scripts/migrate-to-user-snapshots.js
 */

const { sequelize } = require('../src/database');
const { Message, UserRoomGift, UserSnapshot } = require('../src/models/sequelize');
const { v4: uuidv4 } = require('uuid');

async function migrateUserSnapshots() {
  console.log('🚀 开始迁移用户快照数据...');
  
  try {
    // 确保数据库连接
    await sequelize.authenticate();
    console.log('✅ 数据库连接成功');
    
    // 1. 迁移Message表中的用户快照数据
    console.log('📝 开始迁移Message表中的用户快照数据...');
    
    const messages = await sequelize.query(`
      SELECT DISTINCT 
        userId, sessionId, userName, userAvatar, userGender, 
        payGrade, fansLevel, fansClubName, userBorder,
        MIN(createdAt) as firstSeen
      FROM messages 
      WHERE userId IS NOT NULL 
        AND (userName IS NOT NULL OR userAvatar IS NOT NULL)
      GROUP BY userId, sessionId, userName, userAvatar, userGender, 
               payGrade, fansLevel, fansClubName, userBorder
      ORDER BY firstSeen ASC
    `, { type: sequelize.QueryTypes.SELECT });
    
    console.log(`📊 找到 ${messages.length} 条唯一的用户快照记录`);
    
    const snapshotMap = new Map(); // 用于存储快照ID映射
    
    for (const msg of messages) {
      const snapshotId = uuidv4();
      
      // 创建用户快照
      await UserSnapshot.create({
        id: snapshotId,
        userId: msg.userId,
        sessionId: msg.sessionId,
        userName: msg.userName,
        userAvatar: msg.userAvatar,
        userGender: msg.userGender,
        payGrade: msg.payGrade,
        fansLevel: msg.fansLevel,
        fansClubName: msg.fansClubName,
        userBorder: msg.userBorder,
        createdAt: msg.firstSeen
      });
      
      // 创建映射键
      const mapKey = `${msg.userId}_${msg.sessionId}_${msg.userName}_${msg.userAvatar}_${msg.userGender}_${msg.payGrade}_${msg.fansLevel}_${msg.fansClubName}_${msg.userBorder}`;
      snapshotMap.set(mapKey, snapshotId);
    }
    
    console.log('✅ 用户快照创建完成');
    
    // 2. 更新Message表，添加userSnapshotId引用
    console.log('🔄 更新Message表中的userSnapshotId...');
    
    const allMessages = await sequelize.query(`
      SELECT id, userId, sessionId, userName, userAvatar, userGender, 
             payGrade, fansLevel, fansClubName, userBorder
      FROM messages 
      WHERE userId IS NOT NULL
    `, { type: sequelize.QueryTypes.SELECT });
    
    let updatedMessages = 0;
    
    for (const msg of allMessages) {
      const mapKey = `${msg.userId}_${msg.sessionId}_${msg.userName}_${msg.userAvatar}_${msg.userGender}_${msg.payGrade}_${msg.fansLevel}_${msg.fansClubName}_${msg.userBorder}`;
      const snapshotId = snapshotMap.get(mapKey);
      
      if (snapshotId) {
        await sequelize.query(`
          UPDATE messages 
          SET userSnapshotId = :snapshotId 
          WHERE id = :messageId
        `, {
          replacements: { snapshotId, messageId: msg.id },
          type: sequelize.QueryTypes.UPDATE
        });
        updatedMessages++;
      }
    }
    
    console.log(`✅ 更新了 ${updatedMessages} 条Message记录`);
    
    // 3. 迁移UserRoomGift表中的用户快照数据
    console.log('🎁 开始迁移UserRoomGift表中的用户快照数据...');
    
    const giftRecords = await sequelize.query(`
      SELECT DISTINCT 
        userId, sessionId, userName, userAvatar, userGender, 
        payGrade, fansLevel, fansClubName, userBorder,
        MIN(createdAt) as firstSeen
      FROM user_room_gifts 
      WHERE userId IS NOT NULL 
        AND (userName IS NOT NULL OR userAvatar IS NOT NULL)
      GROUP BY userId, sessionId, userName, userAvatar, userGender, 
               payGrade, fansLevel, fansClubName, userBorder
      ORDER BY firstSeen ASC
    `, { type: sequelize.QueryTypes.SELECT });
    
    console.log(`📊 找到 ${giftRecords.length} 条唯一的礼物用户快照记录`);
    
    // 为礼物记录创建快照（如果不存在）
    for (const gift of giftRecords) {
      const mapKey = `${gift.userId}_${gift.sessionId}_${gift.userName}_${gift.userAvatar}_${gift.userGender}_${gift.payGrade}_${gift.fansLevel}_${gift.fansClubName}_${gift.userBorder}`;
      
      if (!snapshotMap.has(mapKey)) {
        const snapshotId = uuidv4();
        
        await UserSnapshot.create({
          id: snapshotId,
          userId: gift.userId,
          sessionId: gift.sessionId,
          userName: gift.userName,
          userAvatar: gift.userAvatar,
          userGender: gift.userGender,
          payGrade: gift.payGrade,
          fansLevel: gift.fansLevel,
          fansClubName: gift.fansClubName,
          userBorder: gift.userBorder,
          createdAt: gift.firstSeen
        });
        
        snapshotMap.set(mapKey, snapshotId);
      }
    }
    
    // 4. 更新UserRoomGift表，添加userSnapshotId引用
    console.log('🔄 更新UserRoomGift表中的userSnapshotId...');
    
    const allGiftRecords = await sequelize.query(`
      SELECT id, userId, sessionId, userName, userAvatar, userGender, 
             payGrade, fansLevel, fansClubName, userBorder
      FROM user_room_gifts 
      WHERE userId IS NOT NULL
    `, { type: sequelize.QueryTypes.SELECT });
    
    let updatedGiftRecords = 0;
    
    for (const gift of allGiftRecords) {
      const mapKey = `${gift.userId}_${gift.sessionId}_${gift.userName}_${gift.userAvatar}_${gift.userGender}_${gift.payGrade}_${gift.fansLevel}_${gift.fansClubName}_${gift.userBorder}`;
      const snapshotId = snapshotMap.get(mapKey);
      
      if (snapshotId) {
        await sequelize.query(`
          UPDATE user_room_gifts 
          SET userSnapshotId = :snapshotId 
          WHERE id = :giftId
        `, {
          replacements: { snapshotId, giftId: gift.id },
          type: sequelize.QueryTypes.UPDATE
        });
        updatedGiftRecords++;
      }
    }
    
    console.log(`✅ 更新了 ${updatedGiftRecords} 条UserRoomGift记录`);
    
    console.log('🎉 数据迁移完成！');
    console.log('📋 迁移摘要：');
    console.log(`   - 创建了 ${snapshotMap.size} 个用户快照`);
    console.log(`   - 更新了 ${updatedMessages} 条消息记录`);
    console.log(`   - 更新了 ${updatedGiftRecords} 条礼物记录`);
    
    console.log('\n⚠️  重要提示：');
    console.log('   1. 迁移完成后，请验证数据的正确性');
    console.log('   2. 确认无误后，可以考虑删除Message和UserRoomGift表中的旧用户快照字段');
    console.log('   3. 建议先在测试环境中验证此迁移脚本');
    
  } catch (error) {
    console.error('❌ 迁移过程中发生错误:', error);
    throw error;
  } finally {
    await sequelize.close();
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  migrateUserSnapshots()
    .then(() => {
      console.log('✅ 迁移脚本执行完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('❌ 迁移脚本执行失败:', error);
      process.exit(1);
    });
}

module.exports = { migrateUserSnapshots };